﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using Model;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class AccountsDAL : IAccountsDAL
    {

        private static readonly string SQL_CONN_SQLSERVER = ConfigurationManager.AppSettings["DBConn"];
        private const string SQL_SELECT_ACCOUNTS = "SELECT AC_accountId, AC_userId, AC_balance, AC_timeOut FROM Accounts";
        private const string SQL_SELECT_ACCOUNT1 = "SELECT AC_accountId, AC_userId, AC_balance, AC_timeOut FROM Accounts WHERE AC_accountId = @accountId";
        private const string SQL_SELECT_ACCOUNT2 = "SELECT AC_accountId, AC_userId, AC_balance, AC_timeOut FROM Accounts WHERE AC_userId = @userId";
        private const string SQL_DELETE_ACCOUNT = "DELETE FROM Accounts WHERE AC_accountId = @accountId";
        private const string SQL_DELETE_ACCOUNT1 = "DELETE FROM Accounts WHERE AC_userId = @userId";
        private const string SQL_UPDATE_ACCOUNT = "UPDATE Accounts SET AC_balance = @balance, AC_timeOut = @timeOut WHERE AC_accountId = @accountId";
        private const string SQL_INSERT_ACCOUNT = "INSERT INTO Accounts VALUES(@userId, @balance, @timeOut)";
        //

        /// <summary>
        /// 根据传入的accountID判断一条数据是否存在
        /// </summary>       
        public bool accountExists(int accountId)
        {
            //Set up a return value
            bool isExist = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@accountId", accountId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT1, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }


        /// <summary>
        /// 根据传入的userID判断一条数据是否存在
        /// </summary>       
        public bool userExists(int userId)
        {
           
            bool isExist = false;           

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT2, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>                
        public bool Add(int userId, int balance, System.DateTime timeOut)
        {
            //Set up a return value
            bool opetation = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", userId), new SqlParameter("@balance", balance), new SqlParameter("@timeOut", timeOut) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_ACCOUNT, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }

        /// <summary>
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation; 
        }

        /// 增加一条数据
        /// </summary>                
        public bool Add(Model.Accounts account)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "INSERT INTO Accounts VALUES(@userId, @balance, @timeOut)";
            Model.Accounts accounts = account;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", accounts.getAC_userId()),  new SqlParameter("@balance", accounts.getAC_balance()), new SqlParameter("@timeOut", accounts.getAC_timeOut()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation; 
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>             
        public bool Update(int accountId, int userId, int balance, System.DateTime timeOut)
        {
            //Set up a return value
            bool opetation = false;         

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@balance", balance), new SqlParameter("@timeOut", timeOut), new SqlParameter("@accountId", accountId) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_UPDATE_ACCOUNT, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation; 
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>             
        public bool Update(Model.Accounts account)
        {
            //Set up a return value
            bool opetation = false;
         
            Model.Accounts accounts = account;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@balance", accounts.getAC_balance()), new SqlParameter("@timeOut", accounts.getAC_timeOut()), new SqlParameter("@accountId", accounts.getAC_accountId()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_UPDATE_ACCOUNT, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation; 
        }


        /// <summary>
        /// 根据传入的accountID删除一条数据
        /// </summary>
        public bool accountDelete(int accountId)
        {
            //Set up a return value
            bool opetation = false;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@accountId", accountId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_DELETE_ACCOUNT, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        /// <summary>
        /// 根据传入的userID删除一条数据
        /// </summary>
        public bool userDelete(int userId)
        {
            //Set up a return value
            bool opetation = false;         

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_DELETE_ACCOUNT1, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        /// <summary>
        /// 根据accountID得到一个对象实体
        /// </summary>

        public Model.Accounts GetModel(int accountId)
        {

            //Set up a return value
            Model.Accounts account = null;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@accountId", accountId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT1, para))
                {
                    if (rdr.Read())

                        account = new Model.Accounts(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), Convert.ToInt32(rdr.GetInt32(2)), Convert.ToDateTime(rdr.GetDateTime(3)));
                    else
                        account = new Model.Accounts();
                }
            }catch(Exception ex){
                throw ex;
            }
            return account;
        }

        /// <summary>
        /// 根据userId得到一个对象实体
        /// </summary>
        public Model.Accounts GetModelByUserId(int userId) 
        {
            //Set up a return value
            Model.Accounts account = null;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT2, para))
                {
                    if (rdr.Read())

                        account = new Model.Accounts(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), Convert.ToInt32(rdr.GetInt32(2)), Convert.ToDateTime(rdr.GetDateTime(3)));
                    else
                        account = new Model.Accounts();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return account;
        }
    }
}
